﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class DAOHocSinh:DataProvider
    {        
        public static DataTable getAll(DTOHocSinh hs)
        {
            ketnoi = Connect();
            OleDbDataAdapter da;
            if (hs == null)
            {
                string select = "select * from hocsinh";
                da = new OleDbDataAdapter(select, ketnoi);
            }
            else
            {
                string select = "select * from hocsinh where malop=@malop";
                da = new OleDbDataAdapter(select, ketnoi);
                da.SelectCommand.Parameters.AddWithValue("@malop", hs.MaLop);
            }
            
            DataSet ds = new DataSet();
            da.Fill(ds, "hocsinh");
            Disconnect();
            return ds.Tables[0];
        }

        public static void Add(DTOHocSinh hs)
        {
            ketnoi = Connect();            
            string select = "select * from hocsinh";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "hocsinh");            

            DataRow dr = ds.Tables[0].NewRow();
            dr["malop"] = hs.MaLop;
            dr["hoten"] = hs.HoTen;
            dr["gioitinh"] = hs.GioiTinh;
            dr["ngaysinh"] = hs.NgaySinh;
            dr["diachi"] = hs.DiaChi;
            dr["email"] = hs.Email;
            dr["ngaytao"] = DateTime.Today;
            ds.Tables[0].Rows.Add(dr);

            da.Update(ds, "hocsinh");
            
            Disconnect();
            DAOLop.UpdateSiso(hs.MaLop,"add");
        }

        public static void Edit(DTOHocSinh hs)
        {
            ketnoi = Connect();
            string select = "select * from hocsinh";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "hocsinh");
            da.Fill(ds, "hocsinh");

            DataRow dr = ds.Tables[0].Rows.Find(hs.MSHS);
            dr.BeginEdit();
            dr["malop"] = hs.MaLop;
            dr["hoten"] = hs.HoTen;
            dr["gioitinh"] = hs.GioiTinh;
            dr["ngaysinh"] = hs.NgaySinh;
            dr["diachi"] = hs.DiaChi;
            dr["email"] = hs.Email;
            dr.EndEdit();

            da.Update(ds, "hocsinh");
            Disconnect();            
        }

        public static void Delete(DTOHocSinh hs)
        {
            ketnoi = Connect();
            string select = "select * from hocsinh";
            OleDbDataAdapter da = new OleDbDataAdapter(select, ketnoi);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source, "hocsinh");
            da.Fill(ds, "hocsinh");

            DataRow dr = ds.Tables[0].Rows.Find(hs.MSHS);            
            dr.Delete();

            da.Update(ds, "hocsinh");
            Disconnect();
            DAOLop.UpdateSiso(hs.MaLop,"delete");
        }

        public static DataTable getHocKy(string hocky)
        {
            ketnoi = Connect();
            //string select = "select mshs,avg(diemtb) as tbhocky from bangdiemmonhoc where `hocky`= \"1\" group by hocky , malop , mshs";
            string select = "select mshs,avg(diemtb) as tbhocky from bangdiemmonhoc where hocky='2' group by hocky , malop , mshs";
            if (hocky == "1")
            {
                select = "select mshs,avg(diemtb) as tbhocky from bangdiemmonhoc where hocky='1' group by hocky , malop , mshs";
            }
            var da = new OleDbDataAdapter(select, ketnoi);            
            new OleDbCommandBuilder(da);            
            
            DataSet ds = new DataSet();
            da.Fill(ds, "HocKy"+hocky);
            Disconnect();
            return ds.Tables[0];
        }
    }
}
